﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ProjetoClasse.Model.Entidade;
using System.Web.Security;
using Classes.Conec;

namespace ProjetoClasse.Model.Dao
{
    public class UsuarioPessoaDAO
    {
        private const String SQL_INSERT_PESSOA = "INSERT INTO USUARIO_PESSOA (NOME, SOBRENOME, IDSEXO, NASCIMENTO, ID_MEMBERSHIP) VALUES (@NOME, @SOBRENOME, @SEXO, @NASCIMENTO, @ID_MEMBERSHIP)";
        private const String SQL_UPDATE_PERFIL = "UPDATE USUARIO_PESSOA SET SITE = @SITE, ESCOLARIDADE = @ESCOLARIDADE, CIDADE = @CIDADE, PAIXOES = @PAIXOES WHERE (ID_MEMBERSHIP = @ID_MEMBERSHIP)";
        private const String SQL_UPDATE_PERFIL_FOTO = "UPDATE USUARIO_PESSOA SET FOTO = @FOTO WHERE (ID_MEMBERSHIP = @ID_MEMBERSHIP)";
        private const String SQL_UPDATE_PERFIL_MSG = "UPDATE USUARIO_PESSOA SET MSG_PERFIL = @MSG WHERE (ID_MEMBERSHIP = @ID_MEMBERSHIP)";
        private const String SQL_SELECT_PERFIL = "SELECT ID_MEMBERSHIP, IDUSUARIO, NOME, SOBRENOME, MSG_PERFIL FROM USUARIO_PESSOA WHERE(ID_MEMBERSHIP = @ID_MEMBERSHIP)";

        private const String SQL_SELECT_PESSOAS = "SELECT NOME, SOBRENOME, ID_MEMBERSHIP FROM USUARIO_PESSOA WHERE NOME LIKE @NOME OR SOBRENOME LIKE @NOME";

        private const String SQL_SELECT_ID_BY_MEMBERSHIP = "SELECT IDUSUARIO FROM USUARIO_PESSOA WHERE ID_MEMBERSHIP = @ID_MEMBERSHIP";

        private const String SQL_SELECT_COMENTARIOS_POR_EVENTO = "SELECT idComentario, id_usuario_pessoa_comentou, comentario, id_evento FROM usuario_pessoa_evento_comentario upec JOIN usuario_pessoa_evento upe ON upec.id_usuario_pessoa_evento = upe.id AND upe.id_usuario_pessoa = @ID_USUARIO AND upe.id_evento = @ID_EVENTO ORDER BY IDCOMENTARIO DESC";

        private const String SQL_SELECT_USUARIO_NOME_BY_ID = "SELECT NOME FROM USUARIO_PESSOA WHERE IDUSUARIO = @IDUSUARIO";

        private const String SQL_SELECT_MEMBERSHIPID_BY_ID = "SELECT ID_MEMBERSHIP FROM USUARIO_PESSOA WHERE IDUSUARIO = @IDUSUARIO";

        private const String SQL_SELECT_CONVITE_AMIZADE = "SELECT STATUS_CONVITE FROM USUARIO_PESSOA_AMIGOS WHERE IDCONVIDA = @IDCONVIDA AND IDCONVIDADO = @IDCONVIDADO OR IDCONVIDADO = @IDCONVIDA AND IDCONVIDA = @IDCONVIDADO";

        private const String SQL_INSERT_CONVITE_AMIZADE = "INSERT INTO USUARIO_PESSOA_AMIGOS(IDCONVIDA, IDCONVIDADO, STATUS_CONVITE) VALUES (@IDCONVIDA, @IDCONVIDADO, 0)";

        private const String SQL_SELECT_SOLICITACOES_AMIZADE = "SELECT UPA.ID, UP.NOME, UP.ID_MEMBERSHIP FROM USUARIO_PESSOA_AMIGOS UPA LEFT JOIN USUARIO_PESSOA UP ON UPA.IDCONVIDA = UP.IDUSUARIO WHERE UPA.IDCONVIDADO = @IDCONVIDADO AND STATUS_CONVITE = 0";

        private const String SQL_UPDATE_SOLICITACAO_AMIZADE = "UPDATE USUARIO_PESSOA_AMIGOS SET STATUS_CONVITE = @RESPOSTA_CONVITE WHERE ID = @ID";

        private const String SQL_DELETE_SOLICITACAO_AMIZADE = "DELETE FROM USUARIO_PESSOA_AMIGOS WHERE ID = @ID";

        private const String SQL_SELECT_AMIGOS = "SELECT UPA.IDCONVIDADO, UP.NOME, UP.ID_MEMBERSHIP FROM USUARIO_PESSOA_AMIGOS UPA LEFT JOIN USUARIO_PESSOA UP ON UP.IDUSUARIO = UPA.IDCONVIDA OR UP.IDUSUARIO = UPA.IDCONVIDADO AND UP.IDUSUARIO <> @ID WHERE UPA.STATUS_CONVITE = 1";

        /// <summary>
        /// Obtém o id do usuario atraves de um Membership User
        /// retorna -1 se nao encontrar usuário
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public Int64 ObterIdUsuario(MembershipUser user)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_ID_BY_MEMBERSHIP, conn);
                cmd.Parameters.AddWithValue("@ID_MEMBERSHIP", user.ProviderUserKey);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return Convert.ToInt64(reader["IDUSUARIO"]);
                }
            }
            return -1;
        }
        /// <summary>
        /// Obtém o id do usuario atraves do id de membership
        /// retorna -1 se nao encontrar usuário
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public Int64 ObterIdUsuario(string idmemberhip)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_ID_BY_MEMBERSHIP, conn);
                cmd.Parameters.AddWithValue("@ID_MEMBERSHIP", idmemberhip);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return Convert.ToInt64(reader["IDUSUARIO"]);
                }
            }
            return -1;
        }

        public List<UsuarioPessoaDTO> PesquisarUsuario(string nome)
        {
            List<UsuarioPessoaDTO> listaUsuarios = new List<UsuarioPessoaDTO>();
            using (SqlConnection conn = Conectar.getConexao())
            {
                SqlCommand cmd;
                try
                {
                    if (nome.Equals("all"))
                    {
                        cmd = new SqlCommand("SELECT NOME, SOBRENOME, ID_MEMBERSHIP FROM USUARIO_PESSOA", conn);

                    }
                    else
                    {
                        cmd = new SqlCommand(SQL_SELECT_PESSOAS, conn);
                    }
                    conn.Open();
                    cmd.Parameters.AddWithValue(@"NOME", "%" + nome + "%");
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        UsuarioPessoaDTO usuario = new UsuarioPessoaDTO();
                        usuario.id_membership = reader["id_membership"].ToString();
                        usuario.nome = reader["nome"].ToString();
                        usuario.sobrenome = reader["sobrenome"].ToString();
                        listaUsuarios.Add(usuario);
                        usuario = null;
                    }
                }
                catch (MembershipCreateUserException ex)
                {
                    throw new Exception(ex.Message);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return listaUsuarios;
        }

        public void UpdateMsgPerfil(string idMembership, string msg)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                SqlTransaction trans = null;
                try
                {
                    SqlCommand cmd = new SqlCommand(SQL_UPDATE_PERFIL_MSG, conn);
                    conn.Open();
                    trans = conn.BeginTransaction();
                    cmd.Transaction = trans;
                    cmd.Parameters.AddWithValue(@"MSG", msg);
                    cmd.Parameters.AddWithValue(@"ID_MEMBERSHIP", idMembership);
                    cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
                }
                catch (MembershipCreateUserException ex)
                {
                    throw new Exception(ex.Message);
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }

        public void InserirFotoPerfil(string foto)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                SqlTransaction trans = null;
                try
                {
                    MembershipUser user = Membership.GetUser(true);
                    SqlCommand cmd = new SqlCommand(SQL_UPDATE_PERFIL_FOTO, conn);
                    conn.Open();
                    trans = conn.BeginTransaction();
                    cmd.Transaction = trans;
                    cmd.Parameters.AddWithValue(@"FOTO", foto);
                    cmd.Parameters.AddWithValue(@"ID_MEMBERSHIP", user.ProviderUserKey);
                    cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
                }
                catch (MembershipCreateUserException ex)
                {
                    throw new Exception(ex.Message);
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }

        public void InserirInfoPerfil(string site, string escolaridade, string cidade, string paixoes)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                SqlTransaction trans = null;
                try
                {
                    MembershipUser user = Membership.GetUser(true);
                    SqlCommand cmd = new SqlCommand(SQL_UPDATE_PERFIL, conn);
                    conn.Open();
                    trans = conn.BeginTransaction();
                    cmd.Transaction = trans;
                    cmd.Parameters.AddWithValue(@"SITE", site);
                    cmd.Parameters.AddWithValue(@"ESCOLARIDADE", escolaridade);
                    cmd.Parameters.AddWithValue(@"CIDADE", cidade);
                    cmd.Parameters.AddWithValue(@"PAIXOES", paixoes);
                    cmd.Parameters.AddWithValue(@"ID_MEMBERSHIP", user.ProviderUserKey);
                    cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
                }
                catch (MembershipCreateUserException ex)
                {
                    throw new Exception(ex.Message);
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }

        public void InserirPessoa(UsuarioPessoaDTO usuario)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                SqlTransaction trans = null;
                try
                {
                    SqlCommand cmd = new SqlCommand(SQL_INSERT_PESSOA, conn);
                    conn.Open();
                    trans = conn.BeginTransaction();
                    cmd.Transaction = trans;
                    MembershipUser user = Membership.CreateUser(usuario.email, usuario.senha, usuario.email);
                    Roles.AddUserToRole(user.Email, "Usuarios");
                    cmd.Parameters.AddWithValue(@"NOME", usuario.nome);
                    cmd.Parameters.AddWithValue(@"SOBRENOME", usuario.sobrenome);
                    cmd.Parameters.AddWithValue(@"SEXO", usuario.sexo);
                    cmd.Parameters.AddWithValue(@"NASCIMENTO", usuario.nascimento);
                    cmd.Parameters.AddWithValue(@"ID_MEMBERSHIP", user.ProviderUserKey);
                    cmd.ExecuteNonQuery();
                    cmd.Transaction.Commit();
                }
                catch (MembershipCreateUserException ex)
                {
                    throw new Exception(ex.Message);
                }
                catch (Exception ex)
                {
                    Membership.DeleteUser(usuario.email);
                    trans.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }

        public UsuarioPessoaDTO ObterPerfil(string idMembership)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(SQL_SELECT_PERFIL, conn);
                    conn.Open();
                    cmd.Parameters.AddWithValue(@"ID_MEMBERSHIP", idMembership);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        UsuarioPessoaDTO usuario = new UsuarioPessoaDTO();
                        usuario.id_membership = reader["id_membership"].ToString();
                        usuario.id = reader["idUsuario"].ToString();
                        usuario.nome = reader["nome"].ToString();
                        usuario.sobrenome = reader["sobrenome"].ToString();
                        usuario.msg_perfil = reader["msg_perfil"].ToString();
                        return usuario;
                    }
                    return null;
                }
                catch (MembershipCreateUserException ex)
                {
                    throw new Exception(ex.Message);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public List<ComentarioEventoDTO> ObterComentariosPorEvento(string idUsuario, int idEvento)
        {
            List<ComentarioEventoDTO> listaComentarios = new List<ComentarioEventoDTO>();
            ComentarioEventoDTO comentarioDTO = null;
            using (SqlConnection conn = Conectar.getConexao())
            {
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(SQL_SELECT_COMENTARIOS_POR_EVENTO, conn);
                    cmd.Parameters.AddWithValue("@ID_USUARIO", idUsuario);
                    cmd.Parameters.AddWithValue("@ID_EVENTO", idEvento);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        comentarioDTO = new ComentarioEventoDTO();
                        comentarioDTO.id_comentario = Convert.ToInt16(reader[0]);
                        comentarioDTO.id_usuario_comentou = Convert.ToInt16(reader[1]);
                        comentarioDTO.comentario = reader[2].ToString();
                        comentarioDTO.id_evento = Convert.ToInt16(reader[3]);
                        listaComentarios.Add(comentarioDTO);
                        comentarioDTO = null;
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return listaComentarios;
        }

        public String ObterNomeUsuarioById(int id)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_USUARIO_NOME_BY_ID, conn);
                cmd.Parameters.AddWithValue("@IDUSUARIO", id);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return reader[0].ToString();
                }
                return null;
            }
        }

        public String ObterMembershipIdByIdUsuario(int id)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_MEMBERSHIPID_BY_ID, conn);
                cmd.Parameters.AddWithValue("@IDUSUARIO", id);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return reader[0].ToString();
                }
                return null;
            }
        }

        public bool VerificarExistenciaConvite(string idVisitante, string idVisitado)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_CONVITE_AMIZADE, conn);
                cmd.Parameters.AddWithValue("@IDCONVIDA", idVisitante);
                cmd.Parameters.AddWithValue("@IDCONVIDADO", idVisitado);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    return true;
                }
            }
            return false;
        }

        public bool ObterStatusConvite(string idVisitante, string idVisitado)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_CONVITE_AMIZADE, conn);
                cmd.Parameters.AddWithValue("@IDCONVIDA", idVisitante);
                cmd.Parameters.AddWithValue("@IDCONVIDADO", idVisitado);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    if (Convert.ToBoolean(reader[0]))
                    {
                        return true;
                    }
                }
                return false;
            }
            throw new Exception("Falha ao obter dados");
        }

        public void EnviarConviteAmizade(string idVisitante, string idVisitado)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_INSERT_CONVITE_AMIZADE, conn);
                cmd.Parameters.AddWithValue("@IDCONVIDA", idVisitante);
                cmd.Parameters.AddWithValue("@IDCONVIDADO", idVisitado);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public List<ConviteAmizadeDTO> ObterSolicitacoesAmizades(string idUsuario)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_SOLICITACOES_AMIZADE, conn);
                cmd.Parameters.AddWithValue("@IDCONVIDADO", idUsuario);
                try
                {
                    List<ConviteAmizadeDTO> listaConvite = new List<ConviteAmizadeDTO>();
                    ConviteAmizadeDTO convite = null;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        convite = new ConviteAmizadeDTO();
                        convite.idconvite = reader[0].ToString();
                        convite.convida = reader[1].ToString();
                        convite.idmembership = reader[2].ToString();
                        listaConvite.Add(convite);
                        convite = null;
                    }

                    return listaConvite;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void UpdateSolicitacaoAmizade(string idConvite, int respostaConvite)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                conn.Open();
                if (respostaConvite == 0)
                {
                    SqlCommand cmd = new SqlCommand(SQL_DELETE_SOLICITACAO_AMIZADE, conn);
                    cmd.Parameters.AddWithValue("@ID", idConvite);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
                else if (respostaConvite == 1)
                {
                    SqlCommand cmd = new SqlCommand(SQL_UPDATE_SOLICITACAO_AMIZADE, conn);
                    cmd.Parameters.AddWithValue("@ID", idConvite);
                    cmd.Parameters.AddWithValue("@RESPOSTA_CONVITE", respostaConvite);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
        }

        public List<UsuarioPessoaDTO> ObterListaAmigos(string idUsuario)
        {
            using (SqlConnection conn = Conectar.getConexao())
            {
                List<UsuarioPessoaDTO> listaAmigos = new List<UsuarioPessoaDTO>();
                UsuarioPessoaDTO amigo = null;
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_AMIGOS, conn);
                cmd.Parameters.AddWithValue("@ID", idUsuario);
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    amigo = new UsuarioPessoaDTO();
                    amigo.id = reader[0].ToString();
                    amigo.nome = reader[1].ToString();
                    amigo.id_membership = reader[2].ToString();
                    listaAmigos.Add(amigo);
                    amigo = null;
                }
                return listaAmigos;
            }
        }
    }
}
